package ca.ualberta.cs.CLRDB;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;

import uk.ac.liv.c3connector.AnnotationModel;
import ca.ualberta.cs.CLRDB.EntityRelManagement;
import ca.ualberta.cs.DB2ExceptionHandling.DB2SqlException;
import ca.ualberta.cs.PrivacyManagement.Notifications;

public class FriendsManagement extends EntityRelManagement{

	public FriendsManagement(){
		mainTableName = "Friend";
	}
	
	@Override
	public void initialize(Statement statement) throws SQLException {
		try{
			statement.executeUpdate("drop table Friend");			
		}
		catch(Exception e){}
		try{			
			statement.executeUpdate("drop table FriendRequests");
		}
		catch(Exception e){}
		
		statement.executeUpdate("CREATE TABLE Friend (" +
				"personId INTEGER NOT NULL, " +  //the follower
				"friendId INTEGER NOT NULL, " +  //the followee
				"CONSTRAINT FK_friendid FOREIGN KEY (friendId) " +
					"REFERENCES Person (ID) ON DELETE CASCADE, " +
				"CONSTRAINT FK_personid FOREIGN KEY (personId) " +
					"REFERENCES Person (ID) ON DELETE CASCADE, " +
				"CONSTRAINT PK_friends PRIMARY KEY (friendId, personId) " +
			") IN CLRSPACE ");
		numOfCols = 2;
		
		/*statement.executeUpdate("CREATE TABLE FriendRequests (" +
				"requester INTEGER NOT NULL, " +
				"requested INTEGER NOT NULL, " +
				"CONSTRAINT FK_requester FOREIGN KEY (requester) " +
					"REFERENCES Person (ID) ON DELETE CASCADE, " +
				"CONSTRAINT FK_requested FOREIGN KEY (requested) " +
					"REFERENCES Person (ID) ON DELETE CASCADE, " +
				"CONSTRAINT PK_request PRIMARY KEY (requester, requested) " +
			") IN CLRSPACE ");*/
	}

	/**
	 * follow someone, also sends a notification to the person being followed
	 * @param username1 (requester)
	 * @param pass1
	 * @param username2 (requested)
	 * @return 1: ok, 2: requested person doesn't exist, 3: user not authenticated, 4: email sending failed but following is added, 5:exception, 6: already following
	 */
	public int follow(String username1, String pass1, String username2){
		//authenticate:
		HashMap<String, String> auth = DatabaseController.personsMgmt.authenticateEncoded(username1, pass1);
		int state = Integer.parseInt(auth.get("state"));
		if( state != 0 )
			return 3;

		Connection con = DatabaseController.connectToDB();
	   	Statement statement = null;
		try{
			int requester = DatabaseController.getPersonsManagement().getPersonId(username1, con);
			int requested = DatabaseController.getPersonsManagement().getPersonId(username2, con);
			if( requested == -1 )
				return 2;
			
			statement = con.createStatement();

			ResultSet rset = statement.executeQuery("SELECT * FROM Friend WHERE " +
					"(personid="+requester+" AND friendid="+requested+") ");
			//if relation doesn't exist
			if(!rset.next()){
				statement.executeUpdate("INSERT INTO Friend (personid,friendid) VALUES ("+requester+","+requested+")");				
			}
			else{
				return 6;
			}
			
			//get email of the requested person:
			ResultSet resEmail = statement.executeQuery("SELECT email FROM Person WHERE ID="+requested);
			String toEmail = "";
			if(resEmail.next())
				toEmail = resEmail.getString("email");
			
			ResultSet rset2 = statement.executeQuery("SELECT email,name,username,affiliation,description FROM Person WHERE ID="+requester);
			if(rset2.next()){
				User friend = new User(rset2.getString("username"), null, rset2.getString("name"), rset2.getString("email"), rset2.getString("affiliation"), rset2.getString("description"));
				int sendEmail = Notifications.sendNewFollowerInfo(friend, toEmail);
				if(sendEmail != 1) //sending notification failed
					return 4;
			}
			
			statement.close();
			con.commit();
			con.close();
		}
		catch(SQLException e){
			DB2SqlException.printSqlErrorMsg(e);
			return 5;
		}
		return 1;

	}
	
	/**
	 * send friendship request ---> not being used in CoRAL anymore. changed to 'following' concept
	 * @param username1 (requester)
	 * @param pass1
	 * @param username2 (requested)
	 * @return 1: ok, 2: requested person doesn't exist, 3: user not authenticated, 4: email sending failed but following is added, 5:exception
	 */
	public int requestFriendship(String username1, String pass1, String username2){
		
		//authenticate:
		HashMap<String, String> auth = DatabaseController.personsMgmt.authenticateEncoded(username1, pass1);
		int state = Integer.parseInt(auth.get("state"));
		if( state != 0 )
			return 3;

		Connection con = DatabaseController.connectToDB();
	   	Statement statement = null;
		try{
			int requester = DatabaseController.getPersonsManagement().getPersonId(username1, con);
			int requested = DatabaseController.getPersonsManagement().getPersonId(username2, con);
			if( requested == -1 )
				return 2;
			
			statement = con.createStatement();
			
			try{
				statement.executeUpdate("INSERT INTO FriendRequests (requester,requested) VALUES ("+requester+","+requested+")");
			}
			catch(SQLException exist){
				if(exist.getErrorCode() != -803 )
					  throw exist;
				//else : doesn't matter, just it is not added again
			}
			
			//get email of the requested person:
			ResultSet resEmail = statement.executeQuery("SELECT email FROM Person WHERE ID="+requested);
			String toEmail = "";
			if(resEmail.next())
				toEmail = resEmail.getString("email");
			
			ResultSet rset = statement.executeQuery("SELECT email,name,username,affiliation,description FROM Person WHERE ID="+requester);
			if(rset.next()){
				User friend = new User(rset.getString("username"), null, rset.getString("name"), rset.getString("email"), rset.getString("affiliation"), rset.getString("description"));
				int sendEmail = Notifications.sendFriendshipRequest(friend, toEmail, requested);
				if(sendEmail != 1) //sending notification failed
					return 4;
			}
			
			statement.close();
			con.commit();
			con.close();
			
		}catch(SQLException e){
			DB2SqlException.printSqlErrorMsg(e);
			return 5;
		}
		return 1;
	}
	
	/**
	 * This will add both directions of the friendship relation in order to ease finding friends later
	 * @param username1 (requested)
	 * @param pass1 (not encrypted - connection should be secure)
	 * @param username2 (requester)
	 * @return 1: ok, 2: one of the users doesn't exist, 3: not authenticated, 4:friendship request not found, 5:exception 
	 */
	public int acceptFriendship(String username1, String pass1, String username2){

		//authenticate:
		HashMap<String, String> auth = DatabaseController.personsMgmt.authenticateEncoded(username1, pass1);
		int state = Integer.parseInt(auth.get("state"));
		if( state != 0 )
			return 3;

		Connection con = DatabaseController.connectToDB();
	   	Statement statement = null;
		try{
			int requested = DatabaseController.getPersonsManagement().getPersonId(username1, con);
			int requester = DatabaseController.getPersonsManagement().getPersonId(username2, con);
			if( requester == -1 )
				return 2;
			
			statement = con.createStatement();
			if(!requestExists(requester, requested, statement))
				return 4;
			
			ResultSet rset = statement.executeQuery("SELECT * FROM Friend WHERE " +
					"(personid="+requested+" AND friendid="+requester+") ");
			//if relation doesn't exist
			if(!rset.next()){
				statement.executeUpdate("INSERT INTO Friend (personid,friendid) VALUES ("+requested+","+requester+")");
				statement.executeUpdate("INSERT INTO Friend (personid,friendid) VALUES ("+requester+","+requested+")");
			}
			
			removeRequest(requester, requested, statement);
			
			statement.close();
			con.commit();
			con.close();
		}
		catch(SQLException e){
			DB2SqlException.printSqlErrorMsg(e);
			return 5;
		}
		return 1;
	}
	
	/**
	 * returns user's friends list with their names, ids, usernames, affiliations, and descriptions
	 * ---> changed to: list of people who this user follows
	 * @param username
	 * @param pass
	 * @return null if user is not authenticated or an exception occurs, otherwise returns array of friends with some info
	 */
	public ArrayList<User> listOfFriends(String username, String pass){
		ArrayList<User> friends = new ArrayList<User>();
		
		//authenticate:
		HashMap<String, String> auth = DatabaseController.personsMgmt.authenticateEncoded(username, pass);
		int state = Integer.parseInt(auth.get("state"));
		if( state != 0 )
			return null;

		Connection con = DatabaseController.connectToDB();
	   	Statement statement = null;
		try{
			int id1 = DatabaseController.getPersonsManagement().getPersonId(username, con);
			
			statement = con.createStatement();	
			ResultSet rset = statement.executeQuery("SELECT * FROM Friend, Person WHERE " +
					"personid="+id1+" AND id=friendid");
			//get friends list - since we have saved both directions, only considering personid works
			while(rset.next()){
				User friend = new User();
				friend.setId(rset.getInt("friendid"));
				friend.setName(rset.getString("name"));
				friend.setUsername(rset.getString("username"));
				friend.setAffiliation(rset.getString("affiliation"));
				friend.setDescription(rset.getString("description"));
				friend.setEmail(rset.getString("email"));
				friends.add(friend);
			}
			
			statement.close();
			con.commit();
			con.close();
		}
		catch(SQLException e){
			DB2SqlException.printSqlErrorMsg(e);	
			return null;
		}
		
		return friends;
	}
	
	/**
	 * returns user's received friendship requests list with their names, ids, usernames, affiliations, and descriptions
	 * @param username
	 * @param pass
	 * @return null if user is not authenticated or an exception occurs, otherwise returns array of requests
	 */
	public ArrayList<User> listFriendshipRequests(String username, String pass){
		ArrayList<User> requests = new ArrayList<User>();
		
		//authenticate:
		HashMap<String, String> auth = DatabaseController.personsMgmt.authenticateEncoded(username, pass);
		int state = Integer.parseInt(auth.get("state"));
		if( state != 0 )
			return null;

		Connection con = DatabaseController.connectToDB();
	   	Statement statement = null;
		try{
			int id1 = DatabaseController.getPersonsManagement().getPersonId(username, con);
			
			statement = con.createStatement();	
			ResultSet rset = statement.executeQuery("SELECT * FROM FriendRequests, Person WHERE " +
					"requested="+id1+" AND id=requester");
			
			System.out.println("requests:");
			while(rset.next()){
				User requester = new User();
				requester.setId(rset.getInt("id"));
				requester.setName(rset.getString("name"));
				requester.setUsername(rset.getString("username"));
				System.out.println(requester.getUsername());
				requester.setAffiliation(rset.getString("affiliation"));
				requester.setDescription(rset.getString("description"));
				requests.add(requester);
			}
			
			statement.close();
			con.commit();
			con.close();
		}
		catch(SQLException e){
			DB2SqlException.printSqlErrorMsg(e);
			return null;
		}
		
		return requests;
	}
	
	private boolean requestExists(int requesterId, int requestedId, Statement statement){
		try{
			ResultSet rset = statement.executeQuery("SELECT * FROM FriendRequests WHERE requester="+requesterId+" AND requested="+requestedId);
			if(rset.next())
				return true;
		}
		catch(SQLException e){
			DB2SqlException.printSqlErrorMsg(e);
		}
		
		return false;
	}
	
	private void removeRequest(int requesterId, int requestedId, Statement statement){
		try{
			statement.executeUpdate("DELETE FROM FriendRequests WHERE requester="+requesterId+" AND requested="+requestedId);			
		}
		catch(SQLException e){
			DB2SqlException.printSqlErrorMsg(e);
		}		
	}
		
}
